جمع کردن و شمارش داده ها بر اساس رنگ سلول ها
سلام به آموزش دیگری از سری آموزش های سافت پلاس خوش آمدید . امروز می خواهیم در مورد این صحبت کنیم که چه جوری می شود به جمع کردن و شمارش داده ها در اکسل بپردازیم .
البته جمع بستن داده ها در اکسل و یا شمارش مقادیر مربوط به سلول ها کار چندان سخت و پیچیده ای نیست . برای هر دوی این کارها توابع بسیار مهم و کاربردی از طرف اکسل تعریف شده است .
برای جمع بستن داده ها در اکسل توابع کاربردی با عنوان Sum داریم . تابع Sum در واقع شامل یک خانواده از توابع هستند که حالت های مختلفی را برای جمع کردن اعداد برای ما ممکن می سازند .
از طرف دیگر برای شمارش مقادیر و داده های مربوط به سلول های یک محدوده اکسل تابع Count را در اختیار ما گذاشته است . تابع Count هم به نوبه خود دارای چند حالت مختلف است که می توانیم از انها استفاده کنیم .
با استفاده از توابع می توانیم حالات مختلفی را در نظر گرفته و با در نظر گرفتن شرط های متعدد کار جمع بستن یا شمارش داده ها را به کمک این توابع انجام دهیم .
و اما داستان این آموزش از کچا شروع شد .
چندی قبل یکی از دوستان فایل اکسلی را در اختیار من گذاشت . در این فایل با استفاده فرمت بندی شرطی تعریف شده بود که هر کالایی که تعداد موجودی آن در انبار به عدد ۱۰ یا کمتر از ۱۰ رسیده بود رنگ سلول مربوط به آن کالا به رنگ قرمز درآید .
و این تبدیل به کابوسی برای وی شده بود . چون در انتهای هر هفته اکسل را باز می کرد و بین ۲۵۰ قلم کالایی که در اکسل ثبت شده بود یکی یکی به دنبال سلول هایی می گشت که رنگ آنها به رنگ قرمز درآمده بود.
چون باید برای تمامی این کالاها درخواست خرید مجدد ثبت می شد . حالا خواسته این دوست من این بود که یک راه حل برای این قضیه پیش پایش بگذارم تا از شر این کار پر زحمت خلاصی پیدا کند .
من برای این کار دو راه حل به ذهنم رسید که با انجام یکی از آنها این مشکل برای وی حل شد .
راه اول استفاده از تابع Countif بود که به کمک ان و با یک شرط ساده از برنامه می خواهیم که فقط کالاهایی را که موجودی ۱۰ و یا کمتر از ۱۰ دارند را برای ما بشمارد .
راه دوم هم این است که با استفاده یک کدنویسی ساده در محیط برنامه نویسی VBA کاری کنیم که برنامه برای ما سلول هایی که به رنگ خاص هستند – مثلا اینجا رنگ قرمز مد نظر بود – را برای ما بشمرد .
و این شد بهانه ای برای آموزش این مطلب که چطور به جمع کردن و شمارش داده ها بر اساس رنگ سلول ها در اکسل بپردازیم .
من علیرضا شهرآئینی هستم و در این مطلب می خواهم راجع به دو چیز با شما صحبت کنم .
– نحوه شمارش سلول ها با رنگ پس زمسنه خاص
– نحوه جمع بستن مقادیر سلول ها بر اساس رنگ پس زمینه آنها .
پس اگر شما هم علاقمند به فراگیری این روش هستید تا انتهای این مطلب با من همراه باشید.
شمارش داده ها بر اساس رنگ پس زمینه سلول ها
برای شروع کار در این خصوص نیاز به یک سری اطلاعات داریم .
من برای این منظور از اطلاعات زیر استفاده می کنم . این فایل در انتهای همین آموزش در قالب یک فایل نمونه مربوط به این آموزش در اختیار شما قرار خواهد گرفت .
همانطور که در این تصویر هم مشخص است ما لیستی از کالاها را داریم . در این لیست کالاهایی با تعداد موجودی کمتر از ۱۰ در سلول هایی به رنگ قرمز کالاهایی با تعداد ۱۰ تا ۲۰ در سلول هایی به رنگ زرد هستند .
سایر کالا هم در سلول های با پس زمینه سفید قرار دارند .
حالا می خواهیم هر کدام از این سلول ها را به تفکیک رنگ آنها شمارش کنیم .
برای شمارش داده ها بر مبنای رنگ پس زمینه سلول هایی که در داخل آنها قرار دارند روش های مختلفی وجود دارد این روش ها در دو دسته کلی قرار می گیرند .
۱- روش اول استفاده از امکانات خود نرم افزار اکسل است .
۲- روش دوم هم شامل استفاده از قابلیت کد نویسی و برنامه نویسی VBA در اکسل می باشد .
در اینجا هر دو تا روش را با هم مرور می کنیم .
روش اول : شمارش داده ها با استفاده از فیلتر کردن و تابع Subtotal
کاری که اینجا باید بکنیم چندان سخت نیست . اول سلول های خود را بر مبنای رنگی که مد نظرمان است فیلتر می کنیم و بعد با کمک و استفاده از تابع Subtotal سلول های فیلتر شده را شمارش می کنیم .
بیایید کار را با استفاده از این روش شروع کنیم .
۱- در جدول بالا ما ۳ نوع سلول داریم . یک دسته از سلول ها سلول هایی هستند به رنگ سفید .یک دسته به رنگ قرمز و دسته دیگر هم به رنگ زرد .
اول به سراغ تابع subtotal می رویم و از آن استفاده می کنیم اما چطوری ؟
در یک سلول که خارج از محدوده سلول های حاوی اطلاعات است یک علامت مساوی بزنید و در داخل آن تابع Subtotal را بصورت زیر درج کنید .
(SUBTOTAL(102,A2:A16=
اما این تابع چه می کند ؟
در این تابع عدد ۱۰۲ ماره مربوط به تابع Count است . بخش دوم تابع هم که محدوده مورد نظر را برای تابع معرفی می کند .
این از اولین مرحله کار در مرحله دوم باید به سراغ استفاده از فیلتر ها برویم . اینجا می خواهیم سلول های خود را بر اساس رنگ مورد نظر خود فیلتر کنیم . رنگ مورد نظر ما در این مرحله رنگ قرمز است .
با استفاده از قابلیت فیلتر کردن سلول های با پس زمینه قرمز را برای خودمان فیلتر می کنیم .
برای فیلتر کردن سلول ها بر اساس رنگ آنها مراحل زیر را دنبال کنید .
۱- اول بر روی اولین سلول از ستون C یعنی همان سلول C1 کلیک کنید تا انتخاب شود .
۲- از سربرگ Home به سراغ داشبورد Editing بروید .
۳ – در این داشبورد گزینه ای داریم به اسم Sort & Filter بر روی ان کلیک کنید تا باز شود .
۴- از بین گزینه هایی که برای شما نمایش داده می شود بر روی گزینه Filter کلیک کنید .
۵- Filter by color گزینه ای است که در این مرحله باید بر روی آن کلیک کنید . با این کار رنگهایی را که در حال حاضر در سلول ها وجود دارند را برای ما نمایش می دهد .
اینجا ما می خواهیم سلول هایی به رنگ قرمز را از بقیه سلول ها فیلتر کنیم . پس بر روی این رنگ کلیک می کنیم . بعد از این کار مشاهده می کنید که فقط سلول های حاوی رنگ قرمز برای ما نمایش داده شده و سایر سلول ها مخفی می شوند .
حالا به سلولی که در داخل آن تابع Subtotal را وارد کردید مراجعه کنید . عددی که در این سلول نمایش داده می شود مساوی تعداد سلول هایی است که در حال حاضر به رنگ قرمز هستند .
همین مراحل را برای رنگ های دیگر هم می توانید تکرار کنید . با هر بار فیلتر کردن سلول ها بر اساس رنگ آنها تابع Subtotal تعداد سلول های مربوط به آن رنگ را برای ما نمایش می دهد .
ترفند اصلی این روش در قابلیت تابع Count نهفته است . این تابع فقط سلول هایی را شمارش می کند که اولا حاوی اطلاعات باشند و در ثانی فقط سلول هایی را قابل مشاهده هستند را برای شمارش در نظر می گیرد .
و چون اینجا ما سلول های مورد نظر خود را بر مبنای رنگ آنها فیلتر می کنیم پس سایر سلول ها که رنگ متفاوت دارند مخفی شده و قاعدتا تابع Count به آنها کاری ندارد .
این اولین روشی بود که با استفاده از امکانات خود اکسل می توانیم به راحتی نسبت به شمارش سلول ها بر مبنای رنگ آنها عمل کنیم .
برای این کار روش دیگری نیز از سوی اکسل وجود دارد که در زیر به آن می پردازیم .
روش دوم: شمارش داده ها با استفاده از دستور Get.cell
توی اکسل یک تابعی داریم به اسم get.cell . اما فرض این تابع با دیگر توابع اکسل این است که شما بصورت مستقیم نمی توانید آن را فراخوانی و از آن استفاده کنید .
صحبت در مورد این تابع و اینکه چه کارهایی می شود با استفاده از آن انجام داد زیاد اما در اینجا فقط به نحوه استفاده از آن برای شمارش داده ها بر مبنای رنگ سلول ها می پردازیم .
در ادامه سه تا مرحله را با هم دنبال می کنیم تا در نهایت با استفاده از این دستور کار شمارش داده ها بر مبنای رنگ سلول ها را با هم انجام بدهیم .
برای این کار به تصویر زیر نگاه کنید .
می خواهیم تعداد اعدادی که به رنگ قرمز هستند را در ستون آخر از این جدول شمارش کنیم .
برای این کار مراحل زیر ر ا دنبال می کنیم .
قدم اول : اولین کاری که باید انجام بدهیم نام گذاری محدوده سلول هایی است که اعداد ما در داخل آن قرار دارند .
برای این کار به سراغ سربرگ formula رفته و بر روی گزینه define name کلیک کنید .
اسم این محدوده را هر چیزی می توانید بگذارید .مثلا اینجا من اسم color_cod را انتخاب کرده ام .
اما در کادر refer to باید این عبارت را درج کنید .
=GET.CELL(38,mysheet!$d2)
حالا می توانید از دستور color_cod استفاده کنید . برای مشاهده توضیحات بیشتر می توانید از ویدئوی آموزشی زیر استفاده کنید .
دیدگاهتان را بنویسید